package com.walker.service.catalogue;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.walker.util.CommonUtils;

@Service
public class CatalogueProjectService {

	@Autowired
	@Qualifier("dmJdbcTemplate")
	private JdbcTemplate dmJdbcTemplate;
	
	/**
	 * 项目列表
	 * @param params
	 * @return
	 */
	public Map<String,Object> queryProjectList(Map<String,Object> params){
		String keyword = CommonUtils.valueOf(params.get("keyword"));
		String busin_id = CommonUtils.valueOf(params.get("busin_id"));
		String space_type_id = CommonUtils.valueOf(params.get("space_type_id"));
		int pageSize = Integer.parseInt(CommonUtils.valueOf(params.get("pageSize")));
		int pageNo = Integer.parseInt(CommonUtils.valueOf(params.get("pageNo")));
		int offset = (pageNo - 1) * pageSize ;
		
		List<Object> paramList = new ArrayList<Object>();
		
		String countSql = getProjectCountSql(paramList,busin_id,space_type_id,keyword);
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");
		
		String sql = getProjectSql(busin_id,space_type_id,keyword);
		paramList.add(pageSize);
		paramList.add(offset);
		List<Map<String,Object>> deriList = dmJdbcTemplate.queryForList(sql,paramList.toArray());
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("total_records", totalRecords);
		data.put("project_data", deriList);
		
		return data;
	}

	/**
	 * 空间类型
	 * @return
	 */
	public List<Map<String,Object>> querySpaceTypeInfo(){
		String sql = "select param_value as space_type_id,param_name as space_type_name from t_meta_public_param where param_key = 'space_type'";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 业务板块
	 * @return
	 */
	public List<Map<String,Object>> queryBusinessFieldInfo(){
		String sql = "select busin_id,busin_name from t_meta_business_field";
		return dmJdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 数据表列表
	 * @param params
	 * @return
	 */
	public Map<String,Object> queryTableList(Map<String,Object> params){
		String project_id = CommonUtils.valueOf(params.get("id"));
		String key_search = CommonUtils.valueOf(params.get("key_search")); 
		int pageSize = Integer.parseInt(CommonUtils.valueOf(params.get("pageSize")));
		int pageNo = Integer.parseInt(CommonUtils.valueOf(params.get("pageNo")));
		int offset = (pageNo - 1) * pageSize;
		
		List<Object> paramList = new ArrayList<Object>();
		String countSql = "select count(1) count from t_meta_cubes where object_type in ('10','11','16','17','18','50') and project_id = ?";
		paramList.add(project_id);
		
		if(StringUtils.isNotBlank(key_search)){
			countSql += " and (cube_code like ? or cube_name like ?)";
			paramList.add(key_search);
			paramList.add(key_search);
		}
		Object totalRecords = dmJdbcTemplate.queryForMap(countSql,paramList.toArray()).get("count");

		String sql = getTableSql(key_search,project_id);
		paramList.add(pageSize);
		paramList.add(offset);
		List<Map<String,Object>> tableList = dmJdbcTemplate.queryForList(sql,paramList.toArray());
		
		StringBuffer baseSql = new StringBuffer();
		baseSql.append("select project.id,project_name,");
		baseSql.append("       bus.busin_name,");
		baseSql.append("       case when space_type = '0' then '基础层' when space_type = '1' then '公共层' when space_type = '2' then '应用层' end as space_type,");
		baseSql.append("       engine.engine_name as offline_engine_name,");
		baseSql.append("       '' as real_time_engine_name,");
		baseSql.append("       (select count(1) table_count from t_meta_cubes cubes where cubes.project_id = project.id and cubes.object_type in('10','11','16','17','18','50')) as table_count,");
		baseSql.append("       (select count(1) user_count from t_meta_project_user b where b.project_id = project.id) as project_user_count,");
		baseSql.append("       (select concat(round(sum(hdfs_size)/1024/1024,2),'MB') from t_asset_v_hive_sum sd where project.project_code = sd.project_code) as hdfs_size,");
		baseSql.append("       project_desc,");
		baseSql.append("       date_format(project.create_time,'%Y-%m-%d %H:%i:%s') as create_time");
		baseSql.append("  from t_meta_project project");
		baseSql.append("  left join t_meta_business_field bus");
		baseSql.append("    on project.busin_id = bus.busin_id");
		baseSql.append("  left join t_meta_compute_engine engine");
		baseSql.append("    on project.engine_id = engine.id");
		baseSql.append("  where project.id = ?");
		Map<String,Object> baseMap = dmJdbcTemplate.queryForMap(baseSql.toString(),project_id);
		
		
		Map<String,Object> data = new HashMap<String,Object>();
		data.put("total_records", totalRecords);
		data.put("table_data", tableList);
		data.put("basic_data", baseMap);
		return data;
	}

	private String getTableSql(String key_search,String project_id) {
		StringBuffer sql = new StringBuffer();
		sql.append("select cubes.cube_code,");
		sql.append("       cubes.cube_name,");
		sql.append("       'Hive' as source_type,");
		sql.append("       case when cyc.cycle_day is null then '30' else cyc.cycle_day end as cycle_day,");
		sql.append("       round(sd.hdfs_size/1024/1024,2) as hdfs_size,");
		sql.append("       date_format(cubes.create_time,'%Y-%m-%d %H:%i:%s') as create_time,");
		sql.append("       cubes.object_type,");
		sql.append("       cubes.oper_user ");
		sql.append("  from t_meta_cubes cubes");
		sql.append("  left join t_meta_project project ");
		sql.append("    on cubes.project_id = project.id ");
		sql.append("  left join t_asset_v_hive_sum sd");
		sql.append("    on project.project_code = sd.project_code");
		sql.append("   and cubes.cube_code = sd.table_code ");
		sql.append("  left join t_meta_object_lifecycle cyc");
		sql.append("    on project.project_code = cyc.project_code ");
		sql.append("   and cubes.cube_code = cyc.object_code ");
		sql.append(" where cubes.object_type in ('10','11','16','17','18','50')");
		sql.append("  and cubes.project_id = ?");
		if(StringUtils.isNotBlank(key_search)){
			sql.append(" and (cubes.cube_code like ? or cubes.cube_name like ?)");
		}
		sql.append(" order by cubes.create_time desc");
		sql.append(" limit ? offset ? ");
		return sql.toString();
	}
	
	private String getProjectSql(String busin_id,String space_type_id,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select project.id,project_code,project_name,");
		sql.append("       bus.busin_name,");
		sql.append("       case when space_type = '0' then '基础层' when space_type = '1' then '公共层' when space_type = '2' then '应用层' end as space_type,");
		sql.append("       engine.engine_name as offline_engine_name,");
		sql.append("       '' as real_time_engine_name,");
		sql.append("       (select count(1) table_count from t_meta_cubes cubes where cubes.project_id = project.id and cubes.object_type in('10','11','16','17','18','50')) as table_count,");
		sql.append("       (select count(1) user_count from t_meta_project_user b where b.project_id = project.id) as project_user_count,");
		sql.append("       (select round(sum(hdfs_size)/1024/1024,2) from t_asset_v_hive_sum sd where project.project_code = sd.project_code) as hdfs_size,");
		sql.append("       project_desc,");
		sql.append("       date_format(project.create_time,'%Y-%m-%d %H:%i:%s') as create_time");
		sql.append("  from t_meta_project project");
		sql.append("  left join t_meta_business_field bus");
		sql.append("    on project.busin_id = bus.busin_id");
		sql.append("  left join t_meta_compute_engine engine");
		sql.append("    on project.engine_id = engine.id");
		sql.append("  where 1 = 1");
		if(StringUtils.isNotBlank(busin_id)){
			sql.append(" and project.busin_id = ?");
		}
		if(StringUtils.isNotBlank(space_type_id)){
			sql.append(" and project.space_type = ?");
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (project_code like ? or project_name like ?)");
		}
		sql.append("  order by project.create_time desc");
		sql.append(" limit ? offset ? ");
		return sql.toString();
	}
	
	private String getProjectCountSql(List<Object> paramList,String busin_id,String space_type_id,String keyword) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count from t_meta_project where 1 = 1");
		if(StringUtils.isNotBlank(busin_id)){
			sql.append(" and busin_id = ?");
			paramList.add(busin_id);
		}
		if(StringUtils.isNotBlank(space_type_id)){
			sql.append(" and space_type = ?");
			paramList.add(space_type_id);
		}
		if(StringUtils.isNotBlank(keyword)){
			sql.append(" and (project_code like ? or project_name like ?)");
			paramList.add("%"+keyword+"%");
			paramList.add("%"+keyword+"%");
		}
		return sql.toString();
	}
}